﻿<%@ WebHandler Language="C#" Class="ZSApsPlanInfoReport" %>

using System;
using System.IO;
using System.Web;
using Newtonsoft.Json;
using Newtonsoft.Json.Linq;
using Newtonsoft.Json.Converters;
using System.Collections.Generic;
using System.Data;
using System.Text;
using System.Net;
using ReportBusiness;

/**
 * Description: 装配厂--APS排产报表
 * Copyright (c) : 通力凯顿（北京）系统集成有限公司
 * Writer:未知
 * Date:未知
 **/

public class ZSApsPlanInfoReport : IHttpHandler
{
    uMESReportOracleDAL dal = new uMESReportOracleDAL();
    public bool IsReusable { get { return false; } }
    public void ProcessRequest(HttpContext context)
    {
        string requestjson = "";
        using (StreamReader reader = new StreamReader(context.Request.InputStream)) { requestjson = reader.ReadToEnd(); }
        JObject jObject = JsonConvert.DeserializeObject(requestjson) as JObject;
        if (jObject["type"] != null)
        {
            try
            {
                Result r = new Result();
                context.Response.ContentType = "text/json";
                string type = jObject["type"].ToString();
                switch (type)
                {
                    case "getData":
                        r = getData(jObject);//查询
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    case "getExportExcel":
                        exportExcel(jObject);//导出
                        break;
                    default:
                        break;
                }
            }
            catch (Exception e)
            {
                Result r = new Result();
                r.data = null;
                r.meta = new Result.Meta(500, e.Message);
                r.total = 0;
                context.Response.ContentType = "text/json";
                context.Response.Write(JsonConvert.SerializeObject(r));
            }
        }
    }

    string getQuerySql(JObject jObject)
    {
        string sql = @"
SELECT f.factoryname,--主制分厂
 wck.workcentername,--工区
 f1.factoryname wspfactoryname,--工序所属分厂
 --to_char(wm_concat(DISTINCT to_char(e3.fullname))) 
 m.mfgordername,--订单号
 en.productname,--件号
 cci.containerno,--任务单号
 en.machineno,--发动机号
 cci.workflowname || ':' || cci.workflowrevision workflowinfo,--工艺及版次
 cci.qty,--数量
 sh.shiftname,--班组
 CASE WHEN instr(ai.apstype,'5')>0 THEN '正排' ELSE '倒排' END apstype,
 wfs.workflowstepname,--工序
 ad.starttime,--加工开始时间
 ad.endtime,--加工结束时间
 DECODE(csd.checkemployeename,NULL,DECODE(csd.reportemployeename,NULL,DECODE(csd.dispatchemployeename,null,decode(ai.status,0,'新建',1,'已下发',2,'待确认',''),'已派工'),'已报工'),'已检验')  status,--状态
 wk.workstationname,--工位（设备编号）
 ad.resstarttime,--工位（设备）开始时间
 ad.resendtime,--工位（设备）结束时间
 '' employeeNames,--人员
 e.fullname apscreatename,--排产人
 csd.dispatchemployeename,--派工人
 to_char(csd.dispatchdate,'yyyy-MM-dd') dispatchdate,--派工时间
 ''works,
 csd.reportemployeename,--报工人
 csd.checkemployeename,--检验员
 en.productdesc,--件号描述（名称）
wk.workstationid,cci.containerid,ad.workflowstepid
 --to_char(wm_concat(DISTINCT to_char(e4.fullname))) works--已派工人
FROM apsplaninfo ai 
LEFT JOIN apsplandetail ad ON ad.apsplaninfoid = ai.apsplaninfoid AND ad.isuse=0
LEFT JOIN containercurrentinfo cci ON cci.containerid=ai.containerid
LEFT JOIN factory f ON f.factoryid = cci.originalfactoryid
LEFT JOIN mfgorder m on m.mfgorderid = cci.mfgorderid
LEFT JOIN workflowstep wfs ON wfs.workflowstepid = ad.workflowstepid 
LEFT JOIN Enginepartplan en ON nvl(en.containerid,en.concontainerid)=ai.containerid
LEFT JOIN Workcenter wck ON wck.workcenterid=en.workcenterid 
LEFT JOIN specbase sb ON sb.specbaseid = wfs.specbaseid
LEFT JOIN spec s ON s.specid = NVL(sb.revofrcdid,wfs.specid)
LEFT JOIN factory f1 ON f1.factoryid=s.factoryid
LEFT JOIN Shift sh ON sh.shiftid=ad.shiftid 
LEFT JOIN Employee e ON e.employeeid=ai.createemployeeid
LEFT JOIN containersteprecord csd ON csd.containerid = cci.containerid AND csd.workflowstepid = wfs.workflowstepid
LEFT JOIN Workstation wk ON wk.workstationid=ad.resourceid
LEFT JOIN Workstationemployee wke ON wke.workstationid=wk.workstationid
LEFT JOIN Employee e3 ON e3.employeeid=wke.employeeid
LEFT JOIN CONTAINERSTEPDISPATCHDETAIL_ZS zs ON zs.containerstepdispatchid=csd.dispatchid
LEFT JOIN Employee e4 ON e4.employeeid=zs.workerid
WHERE ai.isuse=0  AND ai.apstype2 = 2";
        // 分厂
        if (jObject["factoryid"] != null && jObject["factoryid"].ToString() != "")
        {
            sql += string.Format(" AND cci.originalfactoryid = '{0}'", jObject["factoryid"].ToString());
        }

        // 工区
        if (jObject["workcenterid"] != null && jObject["workcenterid"].ToString() != "")
        {
            sql += string.Format(" AND nvl(cci.fromworkcenterid,en.workcenterid) = '{0}'", jObject["workcenterid"].ToString());//--modify by longb 2023-10-19
        }

        // 零件图号
        if (jObject["productname"] != null && jObject["productname"].ToString() != "")
        {
            sql += string.Format(" AND cci.productname = '{0}'", jObject["productname"].ToString());
        }

        // 流水卡号
        if (jObject["containerno"] != null && jObject["containerno"].ToString() != "")
        {
            sql += string.Format(" AND cci.containerno = '{0}'", jObject["containerno"].ToString());
        }

        // 订单号
        if (jObject["mfgordername"] != null && jObject["mfgordername"].ToString() != "")
        {
            sql += string.Format(" AND cci.mfgordername like '%{0}%'", jObject["mfgordername"].ToString());
        }
        //发动机号
        if (jObject["machineno"] != null && jObject["machineno"].ToString() != "")
        {
            sql += string.Format(" AND en.machineno like '%{0}%'", jObject["machineno"].ToString());
        }

        // 工序号
        if (jObject["specno"] != null && jObject["specno"].ToString() != "")
        {
            sql += string.Format(" AND  wfs.workflowstepname LIKE '%{0}%'", jObject["specno"].ToString());
        }

        // 工序名称
        if (jObject["specname"] != null && jObject["specname"].ToString() != "")
        {
            sql += string.Format(" AND  wfs.workflowstepname LIKE '%{0}%'", jObject["specname"].ToString());
        }

        // 工序计划开始时间
        if (jObject["starttime"] != null && jObject["starttime"].ToString() != "")
        {
            JArray arr = JArray.Parse(jObject["starttime"].ToString());
            if (arr.Count >= 2)
            {
                sql += string.Format(" AND ad.starttime >= to_date('{0} 00:00:00','yyyy-MM-dd hh24:mi:ss') ", arr[0].ToString());
                sql += string.Format(" AND ad.starttime <= to_date('{0} 23:59:59','yyyy-MM-dd hh24:mi:ss') ", arr[1].ToString());
            }
        }

        // 工序计划完成时间
        if (jObject["endtime"] != null && jObject["endtime"].ToString() != "")
        {
            JArray arr = JArray.Parse(jObject["endtime"].ToString());
            if (arr.Count >= 2)
            {
                sql += string.Format(" AND ad.endtime >= to_date('{0} 00:00:00','yyyy-MM-dd hh24:mi:ss') ", arr[0].ToString());
                sql += string.Format(" AND ad.endtime <= to_date('{0} 23:59:59','yyyy-MM-dd hh24:mi:ss') ", arr[1].ToString());
            }
        }

        sql += @"GROUP BY  f.factoryname,wck.workcentername, f1.factoryname,wk.workstationname, m.mfgordername,en.machineno, cci.containerno,cci.qty,wfs.workflowstepname, ad.starttime,ad.endtime   
        , ad.resstarttime,ad.resendtime,ai.status,sh.shiftname,e.fullname,csd.dispatchemployeename,to_char(csd.dispatchdate,'yyyy-MM-dd'),ad.workflowstepid
        ，en.productname,en.productdesc,csd.reportemployeename,csd.checkemployeename,cci.workflowname || ':' || cci.workflowrevision,wk.workstationid,cci.containerid,ai.apstype
        ORDER BY to_char(csd.dispatchdate,'yyyy-MM-dd') DESC,cci.containerno,ad.workflowstepid ASC";
        return sql;
    }


    //查询方法
    Result getData(JObject jObject)
    {
        string sql = getQuerySql(jObject);
        uMESPagingDataDTO dto = dal.GetPagingDataIns(sql, (int)jObject["currentPage"], (int)jObject["pageSize"]);
        DataTable dt = dto.DBTable;
        foreach (DataColumn col in dt.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }
        foreach (DataRow row in dt.Rows)
        {
            row["employeenames"] = getEmployeeS(row, "");
            row["works"] = getEmployeeS(row, "1");
        }
        Result r = new Result();
        r.data = dt;
        r.total = Convert.ToInt32(dto.RowCount);
        r.meta = new Result.Meta(200, "请求成功");
        return r;
    }

    string getEmployeeS(DataRow row, string type)
    {
        var sql = @"SELECT  to_char(wm_concat(DISTINCT e3.fullname)) employeeNames FROM 
                    Workstationemployee wke 
                    LEFT JOIN Employee e3 ON e3.employeeid=wke.employeeid
                    WHERE wke.workstationid='" + row["workstationid"] + "'";
        if (type == "1")
        {
            sql = @"SELECT  to_char(wm_concat(DISTINCT e3.fullname)) employeeNames FROM 
                   containersteprecord csd 
                   left join CONTAINERSTEPDISPATCHDETAIL_ZS zs ON zs.containerstepdispatchid=csd.dispatchid
                   LEFT JOIN Employee e3 ON e3.employeeid=zs.workerid
                   WHERE csd.containerid='" + row["containerid"] + "' and csd.workflowstepid='" + row["workflowstepid"] + "'";
        }
        DataTable dt = dal.GetDataTable(sql);
        return dt.Rows.Count > 0 ? dt.Rows[0]["employeeNames"].ToString() : "";
    }

    //导出
    void exportExcel(JObject jObject)
    {

        DataTable dt = dal.GetDataTable(getQuerySql(jObject));
        foreach (DataColumn col in dt.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }
        foreach (DataRow row in dt.Rows)
        {
            row["employeenames"] = getEmployeeS(row, "");
            row["works"] = getEmployeeS(row, "1");
        }
        dt.Columns["factoryname"].Caption = "主制分厂";
        dt.Columns["workcentername"].Caption = "工区";
        dt.Columns["mfgordername"].Caption = "订单号";
        dt.Columns["productname"].Caption = "零件号";
        dt.Columns["containerno"].Caption = "任务单号";
        dt.Columns["machineno"].Caption = "发动机号";
        dt.Columns["workflowinfo"].Caption = "工艺及版次";
        dt.Columns["qty"].Caption = "在制数量";
        dt.Columns["shiftname"].Caption = "班组";
        dt.Columns["apstype"].Caption = "排产策略";
        dt.Columns["workflowstepname"].Caption = "工序";
        dt.Columns["starttime"].Caption = "加工开始时间";
        dt.Columns["endtime"].Caption = "加工结束时间";
        dt.Columns["status"].Caption = "状态";
        dt.Columns["wspfactoryname"].Caption = "工序所属分厂";
        dt.Columns["workstationname"].Caption = "工位（设备编号）";
        dt.Columns["resstarttime"].Caption = "工位（设备）开始时间";
        dt.Columns["resendtime"].Caption = "工位（设备）结束时间";
        dt.Columns["apscreatename"].Caption = "排产人";
        dt.Columns["dispatchemployeename"].Caption = "派工人";
        dt.Columns["dispatchdate"].Caption = "派工时间";
        dt.Columns["works"].Caption = "已派工人";
        dt.Columns["reportemployeename"].Caption = "报工人";
        dt.Columns["checkemployeename"].Caption = "检验员";
        ExportHelper.ExportExcel(dt, "装配厂-- APS排产报表");
    }
}